package dao.impl;

import dao.User_Dao;
import domain.*;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import util.JDBCUtils;

import java.util.*;

public class User_DaoImpl implements User_Dao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    /*通过IMEI查询一个节点的数据*/
    @Override
    public List<AllData> oneNodeData(String IMEI) {
        String sql = "select * from project,node ,data where node.IMEI=? and node.IMEI = data.IMEI and node.project_id = project.project_id";
            List<AllData> oneNodeData = template.query(sql,new BeanPropertyRowMapper<AllData>(AllData.class),IMEI);
        System.out.println("查询了一个节点的数据");
        return oneNodeData;
    }

    /*登录查询*/
    @Override
    public Users userLogin(String username,String password) {
        try {
            String sql = "select * from users where username=? and password=?";
            Users users = template.queryForObject(sql, new BeanPropertyRowMapper<Users>(Users.class), username, password);
            System.out.println("查询了数据");
            return users;

        }catch (Exception e){
            e.printStackTrace();
            System.out.println("出错了");
            return null;
        }
    }
    /*通过id查询项目*/
    @Override
    public Project findOneProject(Object project_id) {
        String sql = "select * from project where project_id =?";
        Project project = template.queryForObject(sql, new BeanPropertyRowMapper<Project>(Project.class),project_id);
        return project;
    }
    /*通过用户id查询用户信息*/
    @Override
    public Users findOneUser(int user_id) {
        String sql = "select * from users where user_id =?";
        Users users = template.queryForObject(sql,new BeanPropertyRowMapper<Users>(Users.class),user_id);
        return users;
    }

    /*查询一个项目数据*/
    @Override
    public List<Data> ProjectData(Object project_id) {
        String sql = "select * from node ,data  where node.project_id =? and  node.IMEI = data.IMEI ";
        List<Data> data = template.query(sql,new BeanPropertyRowMapper<Data>(Data.class),project_id);
        System.out.println("查询了一个项目的数据");
        return data;
    }

    /*查询所有数据*/
    @Override
    public List<AllData> screeningData(Map<String,String[]> screening) {
        String sql = "select * from project,node ,data where node.IMEI = data.IMEI and node.project_id = project.project_id";
        StringBuilder sb = new StringBuilder(sql);
        //定义参数集合
        List<Object> params = new ArrayList<Object>();
        //遍历map对象
        Set<String> keySet = screening.keySet();
        for (String key : keySet)
        {
            //获取value值
            String value = screening.get(key)[0];
            if (value != null && !"".equals(value))
            {
                sb.append(" and "+key+" like ?");
                params.add("%"+value+"%");
            }
        }
        System.out.println(sb.toString());
        System.out.println(params);
        List<AllData> allData = template.query(sb.toString(), new BeanPropertyRowMapper<AllData>(AllData.class),params.toArray());
        System.out.println("查询了所有数据");
        return allData;
    }
    /*查询所有用户*/
    @Override
    public boolean delData(int data_id) {
        try {
            String sql = "delete from data where data_id =? ";
            template.update(sql,data_id);
            System.out.println("删除了一条数据");
            return true;
        }catch (Exception e){
            return false;
        }
    }

    /*查询所有用户*/
    @Override
    public List<Users> findAllUser() {
        String sql = "select * from users ,project where users.project_id = project.project_id order by user_id";
        List<Users> users = template.query(sql, new BeanPropertyRowMapper<Users>(Users.class));
        System.out.println("查询了所有用户数据");
        return users;
    }
/*    *//*查询所有角色*//*
    @Override
    public List<Users> findRole() {
        String sql = "select distinct role from users";
        List<Users> users = template.query(sql, new BeanPropertyRowMapper<Users>(Users.class));
        System.out.println("查询了所有角色");
        return users;
    }*/
    /*查询所有项目*/
    @Override
    public List<Project> findProject() {
        String sql = "select distinct * from project  order by project_id";
        List<Project> projects = template.query(sql, new BeanPropertyRowMapper<Project>(Project.class));
        System.out.println("查询了所有项目");
        return projects;
    }

    /*添加一个项目*/
    @Override
    public boolean addProject(String name, String location) {
        try {
            String sql = "insert into project value(null,?,?)";
            template.update(sql,name,location);
            System.out.println("添加了一个项目");
            return true;
        }catch (Exception e){
            return false;
        }
    }
    /*添加一个用户*/
    @Override
    public boolean addUser(Users users) {
        try {
            String sql = "insert into users value(null,?,?,?,?)";
            template.update(sql,users.getUsername(),users.getPassword(),users.getRole(),users.getProject_id());
            System.out.println("添加了一用户");
            return true;

        }catch (Exception e){
            return false;
        }
    }
    /*删除一个项目*/
    @Override
    public boolean delProject(int id) {
        try {
            String sql = "delete from project where project_id =? ";
            template.update(sql,id);
            System.out.println("删除了一个项目");
            return true;
        }catch (Exception e){
            return false;
        }
    }

    /*删除一个用户*/
    @Override
    public boolean delUser(int id) {
        try {
            String sql = "delete from users where user_id=?";
            template.update(sql,id);
            System.out.println("删除了一个用户");
            return true;
        }catch (Exception e)
        {
            return false;
        }
    }
    /*修改项目信息*/
    @Override
    public boolean upProject(Project project) {
        try {
            String sql = "update project set  project_name=? , Project_location=? where project_id=? ";
            template.update(sql,project.getProject_name(),project.getProject_location(),project.getProject_id());
            System.out.println("修改了项目信息");
            return true;
        }catch (Exception e) {
            return false;
        }
    }
    /*修改用户信息*/
    @Override
    public boolean upUser(Users users) {
        try {
            String sql = "update users set username=? ,password=? , role=? , project_id=? where user_id=? ";
            template.update(sql,users.getUsername(),users.getPassword(),users.getRole(),users.getProject_id(),users.getUser_id());
            System.out.println("修改了用户信息");
            return true;
        }catch (Exception e) {
            return false;
        }
    }
    /*查询所有节点信息*/
    @Override
    public List<Node> selectAllNode() {
        String sql = "select * from node,project where node.project_id=project.project_id";
        List<Node> nodes = template.query(sql,new BeanPropertyRowMapper<Node>(Node.class));
        System.out.println("查询了所有节点信息");
        return nodes;
    }

    /*删除一个选中的节点*/
    @Override
    public boolean deleNode(int node_id) {
        try {
            String sql = "delete from node where node_id=?";
            template.update(sql,node_id);
            System.out.println("删除了一个节点");
            return true;
        }catch (Exception e)
        {
            return false;
        }
    }
    /*通过ID查询一个节点信息*/
    @Override
    public Node findOneNode(int node_id) {
        String sql = "select * from node where node_id =?";
        Node node = template.queryForObject(sql,new BeanPropertyRowMapper<Node>(Node.class),node_id);
        return node;
    }
    /*修改节点信息*/
    @Override
    public boolean upNode(Node node) {
        try {
            String sql = "update node set  IMEI=? , node_location=? , project_id =? where node_id=? ";
            template.update(sql,node.getIMEI(),node.getNode_location(),node.getProject_id(),node.getNode_id());
            System.out.println("修改了节点信息");
            return true;
        }catch (Exception e) {
            return false;
        }
    }

    /*添加一个节点*/
    @Override
    public boolean addNode(String IMEI, String node_location, int project_id ,boolean init) {
        try {
            String sql = "insert into node value(null,?,?,?,?)";
            template.update(sql,IMEI,node_location,project_id,init);
            System.out.println("添加了一个节点");
            return true;
        }catch (Exception e){
            return false;
        }
    }

    /*查询总的数据条数*/

    @Override
    public int findDataCount() {
        String sql = "select count(*) from data";
        int count = template.queryForObject(sql,Integer.class);
        return count;
    }

    /*查询一页数据*/
    @Override
    public List<AllData> findDataPage(int start, int rows) {
        String sql = "select * from project,node ,data where node.IMEI = data.IMEI and node.project_id = project.project_id limit ?, ?";
        List<AllData> pageData = template.query(sql,new BeanPropertyRowMapper<AllData>(AllData.class),start,rows);
        return pageData;
    }

    /*查询预警节点值*/
    @Override
    public List<Warning> selectWarning() {

        String sql = "select *,sum(data) as sum " +
                "from data,node,project " +
                "where node.IMEI = data.IMEI and node.project_id = project.project_id and data.check=0 and data.IMEI " +
                "in (select data.IMEI from data group by data.IMEI having count(data.IMEI) >1 )  " +
                "group by data.IMEI  having sum(data)>=2";
        List<Warning> warnings = template.query(sql,new BeanPropertyRowMapper<Warning>(Warning.class));
        System.out.println("查询了需要更新的数据");
        return warnings;
    }

    /*更新预警的节点*/
    @Override
    public boolean updateWarning(String IMEI) {
        try {
            String sql = "update `data` set `check`=1 where IMEI=? ";
            template.update(sql,IMEI);
            System.out.println("预警节点更新了");
            return true;
        }catch (Exception e) {
            return false;
        }
    }

    /*通过IMEI查询节点的数据和时间*/
    @Override
    public List<NodeView> findNodeView(String IMEI) {
        String sql = "select * from data where IMEI=? ";
        List<NodeView> nodeViews = template.query(sql,new BeanPropertyRowMapper<NodeView>(NodeView.class),IMEI);
        System.out.println("查询了一个节点的全部数据");
        return nodeViews;
    }

    /*施工员查看自己所在项目的需要更新的节点*/
    @Override
    public List<Warning> editorUp(int project_id) {
        String sql = "select *,sum(data) as sum " +
                "from data,node,project " +
                "where project.project_id=? and node.IMEI = data.IMEI and node.project_id = project.project_id and data.check=0 and data.IMEI " +
                "in (select data.IMEI from data group by data.IMEI having count(data.IMEI) >1 )  " +
                "group by data.IMEI  having sum(data)>=2";
        List<Warning> warnings = template.query(sql,new BeanPropertyRowMapper<Warning>(Warning.class),project_id);
        System.out.println("查询了需要更新的数据");
        return warnings;
    }


}
